﻿/******************************
Copyright (c) 2009, Avanade
All rights reserved.

Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:

* Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.

* Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.

* Neither the name of Avanade nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS 
FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, 
BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT 
LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. 
******************************/

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;

namespace PhpSiteGenerator
{
    /// <summary>
    /// Handles logic for Creating the php site based on the given DatabaseInfo object
    /// </summary>
    public static class SiteGenerator
    {
        
        public static void GenerateSite(DatabaseInfo database, CssProperties cssProperties)
        {
            GenerateDatabasePage(database);         
            GenerateEditPage(database);
            GenerateCreatePage(database);
            GenerateCssStylesPage(database, cssProperties);
            GenerateStaticPage(database, "SiteTemplates", "utilities.php");

            //Based on user input decide which select page to output
            //There are four options/templates sortingandpaging, paging, sorting, and none
            if (database.EnablePaging && database.EnableSorting)
            {
                GenerateSelectPageWithPagingAndSorting(database);
            }
            else if(database.EnableSorting)
            {
                GenerateSelectPageWithSorting(database);
            }
            else if(database.EnablePaging)
            {
                GenerateSelectPageWithPaging(database);
            }
            else
            {
                GenerateSelectPage(database);
            }
        }

        /// <summary>
        /// Creates the database.php file and saves it to the file system
        /// </summary>
        /// <param name="database">The database configuration</param>
        public static void GenerateDatabasePage(DatabaseInfo database)
        {   
            StreamReader dbFileReader = new StreamReader("SiteTemplates\\database.php.txt");
            StreamWriter dbFileWriter = new StreamWriter(Path.Combine(database.SaveFilesDir, "database.php"));

            try
            {
                //Read the file into a string
                string file = dbFileReader.ReadToEnd();

                //Replace tags in the file with configured data
                file = file.Replace("{SERVER}", database.ServerName);
                file = file.Replace("{USER}", database.UserName);
                file = file.Replace("{PASSWORD}", database.Password);
                file = file.Replace("{DATABASE}", database.DatabaseName);

                //Write the file to disk
                dbFileWriter.Write(file);
            }
            catch(Exception e)
            {
                throw e;
            }
            finally
            {
                dbFileReader.Close();
                dbFileWriter.Close();
            }
        }

        /// <summary>
        /// Creates the index.php with no sorting or paging file and saves it to the file system
        /// </summary>
        /// <param name="database">The database configuration</param>
        private static void GenerateSelectPage(DatabaseInfo database)
        {
            // date validation reference
            string validationLogic = null;

            StreamReader selectFileReader = new StreamReader("SiteTemplates\\index.php.txt");
            StreamWriter selectFileWriter = new StreamWriter(Path.Combine(database.SaveFilesDir, "index.php"));

            try
            {
                //Read the file into a string
                string file = selectFileReader.ReadToEnd();

                // build validation logic
                validationLogic = BuildDateValidationLogic(validationLogic, database);

                // set $valid variable according to whether or not validation will be done
                if (!string.IsNullOrEmpty(validationLogic))
                {
                    file = file.Replace("{ISVALID}", "false");
                    file = file.Replace("{DATEVALIDATION}", validationLogic);
                }
                else 
                {
                    file = file.Replace("{ISVALID}", "true");
                    file = file.Replace("{DATEVALIDATION}", "");
                }

                //Replace tags in the file with configured data
                file = file.Replace("{UPDATEQUERY}", BuildUpdateQuery(database));
                file = file.Replace("{INSERTQUERY}", BuildInsertQuery(database));
                file = file.Replace("{DELETEQUERY}", BuildDeleteQuery(database));
                file = file.Replace("{SELECTQUERY}", BuildSelectQuery(database));
                file = file.Replace("{TABLEROW}", BuildTableRow(database));
                file = file.Replace("{TABLEHEADER}", BuildTableHeader(database));
                file = file.Replace("{PKCOLUMN}", database.PKColumn.ToString());
                file = file.Replace("{DATAPARAMS}", BuildDataParameters(database));

                //Write the file to disk
                selectFileWriter.Write(file);
            }
            finally
            {
                selectFileReader.Close();
                selectFileWriter.Close();
            }
        }

        /// <summary>
        /// Creates the index.php file with paging/sorting and saves it to the file system
        /// </summary>
        /// <param name="database">The database configuration</param>
        private static void GenerateSelectPageWithPagingAndSorting(DatabaseInfo database)
        {
            // date validation reference
            string validationLogic = null;

            StreamReader selectFileReader = new StreamReader("SiteTemplates\\selectwithpagingandsorting.php.txt");
            StreamWriter selectFileWriter = new StreamWriter(Path.Combine(database.SaveFilesDir, "index.php"));

            try
            {
                //Read the file into a string
                string file = selectFileReader.ReadToEnd();

                // build validation logic
                validationLogic = BuildDateValidationLogic(validationLogic, database);

                // set $valid variable according to whether or not validation will be done
                if (!string.IsNullOrEmpty(validationLogic))
                {
                    file = file.Replace("{ISVALID}", "false");
                    file = file.Replace("{DATEVALIDATION}", validationLogic);
                }
                else
                {
                    file = file.Replace("{ISVALID}", "true");
                    file = file.Replace("{DATEVALIDATION}", "");
                }

                //Replace tags in the file with configured data
                file = file.Replace("{UPDATEQUERY}", BuildUpdateQuery(database));
                file = file.Replace("{INSERTQUERY}", BuildInsertQuery(database));
                file = file.Replace("{DELETEQUERY}", BuildDeleteQuery(database));
                file = file.Replace("{SELECTQUERY}", BuildSelectQuery(database));
                file = file.Replace("{TABLEROW}", BuildTableRow(database));
                file = file.Replace("{TABLEHEADER}", BuildTableHeader(database));
                file = file.Replace("{VALIDATIONSTRING}", BuildValidationString(database));
                file = file.Replace("{ROWCOUNT}", database.NumberOfRowsPerPage.ToString());
                file = file.Replace("{TABLENAME}", database.TableName.ToString());
                file = file.Replace("{DEFAULTSORT}", database.PKColumn.ToString());
                file = file.Replace("{PKCOLUMN}", database.PKColumn.ToString());
                file = file.Replace("{DATAPARAMS}", BuildDataParameters(database));


                //Write the file to disk
                selectFileWriter.Write(file);
            }
            finally
            {
                selectFileReader.Close();
                selectFileWriter.Close();
            }
        }


        /// <summary>
        /// Creates the index.php file with paging and saves it to the file system
        /// </summary>
        /// <param name="database">The database configuration</param>
        private static void GenerateSelectPageWithPaging(DatabaseInfo database)
        {
            // date validation reference
            string validationLogic = null;

            StreamReader selectFileReader = new StreamReader("SiteTemplates\\selectwithpaging.php.txt");
            StreamWriter selectFileWriter = new StreamWriter(Path.Combine(database.SaveFilesDir, "index.php"));

            try
            {
                //Read the file into a string
                string file = selectFileReader.ReadToEnd();

                // build validation logic
                validationLogic = BuildDateValidationLogic(validationLogic, database);

                // set $valid variable according to whether or not validation will be done
                if (!string.IsNullOrEmpty(validationLogic))
                {
                    file = file.Replace("{ISVALID}", "false");
                    file = file.Replace("{DATEVALIDATION}", validationLogic);
                }
                else
                {
                    file = file.Replace("{ISVALID}", "true");
                    file = file.Replace("{DATEVALIDATION}", "");
                }

                //Replace tags in the file with configured data
                file = file.Replace("{UPDATEQUERY}", BuildUpdateQuery(database));
                file = file.Replace("{INSERTQUERY}", BuildInsertQuery(database));
                file = file.Replace("{DELETEQUERY}", BuildDeleteQuery(database));
                file = file.Replace("{SELECTQUERY}", BuildSelectQuery(database));
                file = file.Replace("{TABLEROW}", BuildTableRow(database));
                file = file.Replace("{TABLEHEADER}", BuildTableHeader(database));
                file = file.Replace("{ROWCOUNT}", database.NumberOfRowsPerPage.ToString());
                file = file.Replace("{TABLENAME}", database.TableName.ToString());
                file = file.Replace("{PKCOLUMN}", database.PKColumn.ToString());
                file = file.Replace("{DATAPARAMS}", BuildDataParameters(database));

                //Write the file to disk
                selectFileWriter.Write(file);
            }
            finally
            {
                selectFileReader.Close();
                selectFileWriter.Close();
            }
        }

        /// <summary>
        /// Creates the index.php file with sorting and saves it to the file system
        /// </summary>
        /// <param name="database">The database configuration</param>
        private static void GenerateSelectPageWithSorting(DatabaseInfo database)
        {
            // date validation reference
            string validationLogic = null;

            StreamReader selectFileReader = new StreamReader("SiteTemplates\\selectwithsorting.php.txt");
            StreamWriter selectFileWriter = new StreamWriter(Path.Combine(database.SaveFilesDir, "index.php"));

            try
            {
                //Read the file into a string
                string file = selectFileReader.ReadToEnd();

                // build validation logic
                validationLogic = BuildDateValidationLogic(validationLogic, database);

                // set $valid variable according to whether or not validation will be done
                if (!string.IsNullOrEmpty(validationLogic))
                {
                    file = file.Replace("{ISVALID}", "false");
                    file = file.Replace("{DATEVALIDATION}", validationLogic);
                }
                else
                {
                    file = file.Replace("{ISVALID}", "true");
                    file = file.Replace("{DATEVALIDATION}", "");
                }

                //Replace tags in the file with configured data
                file = file.Replace("{UPDATEQUERY}", BuildUpdateQuery(database));
                file = file.Replace("{INSERTQUERY}", BuildInsertQuery(database));
                file = file.Replace("{DELETEQUERY}", BuildDeleteQuery(database));
                file = file.Replace("{SELECTQUERY}", BuildSelectQuery(database));
                file = file.Replace("{TABLEROW}", BuildTableRow(database));
                file = file.Replace("{TABLEHEADER}", BuildTableHeader(database));
                file = file.Replace("{VALIDATIONSTRING}", BuildValidationString(database));
                file = file.Replace("{TABLENAME}", database.TableName.ToString());
                file = file.Replace("{DEFAULTSORT}", database.PKColumn.ToString());
                file = file.Replace("{PKCOLUMN}", database.PKColumn.ToString());
                file = file.Replace("{DATAPARAMS}", BuildDataParameters(database));


                //Write the file to disk
                selectFileWriter.Write(file);
            }
            finally
            {
                selectFileReader.Close();
                selectFileWriter.Close();
            }
        }
        /// <summary>
        /// Creates the edit.php file and saves it to the file system
        /// </summary>
        /// <param name="database">The database configuration</param>
        private static void GenerateEditPage(DatabaseInfo database)
        {
            StreamReader editFileReader = new StreamReader("SiteTemplates\\edit.php.txt");
            StreamWriter editFileWriter = new StreamWriter(Path.Combine(database.SaveFilesDir, "edit.php"));

            try
            {
                //Read the file into a string
                string file = editFileReader.ReadToEnd();

                //Replace tags in the file with configured data
                file = file.Replace("{EDITSELECTQUERY}", BuildEditSelectQuery(database));
                file = file.Replace("{HIDDENPKFIELD}", BuildHiddenPKField(database));
                file = file.Replace("{INPUTFIELDS}", BuildEditInputFields(database));
                file = file.Replace("{PKCOLUMN}", database.PKColumn.ToString());

                //Write the file to disk
                editFileWriter.Write(file);
            }
            finally
            {
                editFileReader.Close();
                editFileWriter.Close();
            }
        }

        /// <summary>
        /// Creates the create.php file and saves it to the file system
        /// </summary>
        /// <param name="database">The database configuration</param>
        private static void GenerateCreatePage(DatabaseInfo database)
        {
            StreamReader createFileReader = new StreamReader("SiteTemplates\\create.php.txt");
            StreamWriter createFileWriter = new StreamWriter(Path.Combine(database.SaveFilesDir, "create.php"));

            try
            {
                //Read the file into a string
                string file = createFileReader.ReadToEnd();

                //Replace tags in the file with configured data
                file = file.Replace("{CREATEINPUTFIELDS}", BuildCreateInputFields(database));

                //Write the file to disk
                createFileWriter.Write(file);
            }
            finally
            {
                createFileReader.Close();
                createFileWriter.Close();
            }
        }

        /// <summary>
        /// Creates the Style.css file and saves it to the file system
        /// </summary>
        /// <param name="database">The database configuration</param>
        /// <param name="cssProperties">A list of user entered CSS styles</param>
        private static void GenerateCssStylesPage(DatabaseInfo database, CssProperties cssProperties)
        {
            StreamReader editFileReader = new StreamReader("SiteTemplates\\style.css.txt");
            StreamWriter editFileWriter = new StreamWriter(Path.Combine(database.SaveFilesDir, "style.css"));

            try
            {
                string templatefile = editFileReader.ReadToEnd();
                StringBuilder file = new StringBuilder();

                foreach (CssStyle cssList in cssProperties.Styles)
                {
                    string templatefilecopy = templatefile;
                    templatefilecopy = templatefile.Replace("{NAME}", cssList.CssName);
                    file.Append(templatefilecopy.Replace("{VALUES}", BuildCssNameValuePairsString(cssList)).ToString());                  
                }

                //Write the file to disk
                editFileWriter.Write(file);
            }
            finally
            {
                editFileReader.Close();
                editFileWriter.Close();
            }
        }

        /// <summary>
        /// Generates the Update query for the site based on the database configuration
        /// </summary>
        /// <param name="database">The database configuration</param>
        /// <returns>Example: "UPDATE Person SET FirstName = '" . $_GET["firstname"] . "', LastName = '" . $_GET["lastname"] . "', Age = '" . $_GET["age"] . "' WHERE Id = " . $_GET["id"];</returns>
        public static string BuildUpdateQuery(DatabaseInfo database)
        {
            //Baseline update
            string updateQuery = "UPDATE {0} SET {1} WHERE {2} = ?";

            //Create each column update based on selected columns
            StringBuilder updateColumns = new StringBuilder();

            int counter = 0;

            foreach(TableColumn column in database.Columns)
            {
                //Do not update the primary key column
                if (column.ColumnName != database.PKColumn)
                {
                    //Only add comma after the first column
                    if (counter != 0)
                    {
                        updateColumns.Append(", ");
                    }

                    //Add column to column string
                    updateColumns.Append(column.ColumnName + " = ? ");
                    counter++; 
                }
            }

 
            return String.Format(updateQuery, database.TableName, updateColumns.ToString(), database.PKColumn);
        }

        /// <summary>
        /// Generates the Insert query for the site based on the database configuration
        /// </summary>
        /// <param name="database">The database configuration</param>
        /// <returns>Example: INSERT INTO Person (FirstName, LastName, Age) VALUES ('" . $_GET["firstname"] . "','" . $_GET["lastname"] . "','" . $_GET["age"] . "')</returns>
        public static string BuildInsertQuery(DatabaseInfo database)
        {
            //Baseline Insert
            string insertQuery = "INSERT INTO {0} ({1}) VALUES ({2})";

            //Build name set for insert
            StringBuilder nameColumns = new StringBuilder();

            int nameColumnsCounter = 0;

            foreach (TableColumn column in database.Columns)
            {
                //Do not update the primary key column
                if (column.ColumnName != database.PKColumn)
                {
                    //Only add comma after the first column
                    if (nameColumnsCounter != 0)
                    {
                        nameColumns.Append(", ");
                    }

                    nameColumns.Append(column.ColumnName);
                    nameColumnsCounter++;
                }
            }

            //Build value set for Insert
            StringBuilder valueColumns = new StringBuilder();

            int valueColumnsCounter = 0;

            
            foreach (TableColumn column in database.Columns)
            {
                //Do not insert the primary key column
                if (column.ColumnName != database.PKColumn)
                {
                    //Only add comma after the first column
                    if (valueColumnsCounter != 0)
                    {
                        valueColumns.Append(", ");
                    }

                    valueColumns.Append("?");
                    valueColumnsCounter++;
                }
            }

            return String.Format(insertQuery, database.TableName, nameColumns.ToString(), valueColumns.ToString());
        }

        /// <summary>
        /// Generates the Delete query for the site based on the database configuration
        /// </summary>
        /// <param name="database">The database configuration</param>
        /// <returns>Example: DELETE FROM Person WHERE Id=" . $_GET["Id"]</returns>
        public static string BuildDeleteQuery(DatabaseInfo database)
        {
            //Baseline Delete
            string deleteQuery = "DELETE FROM {0} WHERE {1} = ?";

            return String.Format(deleteQuery, database.TableName, database.PKColumn);
        }

        /// <summary>
        /// Generates the Select query for the site based on the database configuration and user options for paging and sorting
        /// </summary>
        /// <param name="database">The database configuration</param>
        /// <returns>Example: SELECT Id, FirstName, LastName, Age FROM Person</returns>
        public static string BuildSelectQuery(DatabaseInfo database)
        {
            //Baseline Select
            string selectQuery = "SELECT {0} FROM {1}";

            if( database.EnableSorting && database.EnablePaging)
                selectQuery = "WITH OrderedFields AS (SELECT {0}, ROW_NUMBER() OVER (ORDER BY \" . $sortBy . \" ) AS 'RowNumber' FROM {1}) SELECT * FROM OrderedFields WHERE RowNumber BETWEEN ? AND ?";
            
            else if(database.EnablePaging)
                selectQuery = "WITH OrderedFields AS (SELECT {0}, ROW_NUMBER() OVER (ORDER BY " + database.PKColumn + " ) AS 'RowNumber' FROM {1}) SELECT * FROM OrderedFields WHERE RowNumber BETWEEN ? AND ?";

            else if (database.EnableSorting)
                selectQuery = "SELECT {0} FROM {1} ORDER BY \" . $sortBy . \"";


            //Build columns to select
            StringBuilder selectColumns = new StringBuilder();

            int selectColumnsCounter = 0;

            foreach (TableColumn column in database.Columns)
            {
                //Only add comma after the first column
                if (selectColumnsCounter != 0)
                {
                    selectColumns.Append(", ");
                }

                selectColumns.Append(column.ColumnName);
                selectColumnsCounter++;
            }

            return String.Format(selectQuery, selectColumns.ToString(), database.TableName);
        }

        /// <summary>
        /// Build the query parameters for both update and insert statements
        /// </summary>
        /// <param name="database">The database configuration</param>
        /// <returns>Example: array($_GET["OrderID"], $_GET["CustomerID"])</returns>
        public static string BuildDataParameters(DatabaseInfo database)
        {
            //Create each column as a parameter based on selected columns
            StringBuilder paramsBuilder = new StringBuilder();

            bool pkIsLast = false;

            if (database.Columns[database.Columns.Count -1].ColumnName == database.PKColumn)
            {
                pkIsLast = true;
            }

            for (int i = 0; i < database.Columns.Count; i++ )
            {

                //Do not include the primary key column
                if (database.Columns[i].ColumnName != database.PKColumn)
                {
                    // if this table column contains a datetime, format the date field
                    if (database.Columns[1].ColumnType == "datetime")
                    {
                        paramsBuilder.Append("$" + database.Columns[i].ColumnName);
                    }
                    else
                    {
                        //Add column to column string
                        paramsBuilder.Append("htmlentities($_GET[\"" + database.Columns[i].ColumnName + "\"],ENT_COMPAT,'iso-8859-1')");
                    }

                    if (i < database.Columns.Count - 1 && pkIsLast == false)
                    {
                        paramsBuilder.Append(", ");
                    }
                    else if(i < (database.Columns.Count - 2))
                    {
                        paramsBuilder.Append(", ");
                    }
                }

            }

            return paramsBuilder.ToString();
        }

       

        /// <summary>
        /// Generates the validation string for use in paging parameters
        /// </summary>
        /// <param name="database">The database configuration</param>
        /// <returns>$sort == 'FirstName' ||  $sort == 'LastName'</returns>
        public static string BuildValidationString(DatabaseInfo database)
        {

            //Build columns to select
            StringBuilder selectColumns = new StringBuilder();

            int selectColumnsCounter = 1;

            foreach (TableColumn column in database.Columns)
            {
                
                selectColumns.Append("$sort == '" + column.ColumnName + "'");

                //add || on all but last
                if (selectColumnsCounter != database.Columns.Count)
                {
                    selectColumns.Append(" || ");
                }
                selectColumnsCounter++;
            }

            return selectColumns.ToString();
        }

        /// <summary>
        /// Generates the table markup, including Edit and Delete links for the index.php page
        /// </summary>
        /// <param name="database">The database configuration</param>
        /// <returns>Example: echo "<tr><td>" . $row["FirstName"] . "</td><td>" . $row["LastName"] . "</td><td>" . $row["Age"] . "</td><td><a href='Edit.php?Id=" . $row["Id"] . "'>Edit</a></td><td><a href='index.php?action=delete&Id=" . $row["Id"] . "'>Delete</a></td></tr>";
        /// </returns>
        private static string BuildTableRow(DatabaseInfo database)
        {
            //Build the row template
            StringBuilder row = new StringBuilder();

            foreach (TableColumn column in database.Columns)
            {
                //Do not display the primary key column
                if (column.ColumnName != database.PKColumn)
                {
                    row.Append("<td class='tblCell'>");

                    // if this column / field is a dateimte, format output
                    if (column.ColumnType == "datetime")
                    {
                        row.Append(String.Format("\" . (isset($row[\"{0}\"]) ? $row[\"{0}\"]->format('Y-m-d H:i:s') : \"\") . \"</td>", column.ColumnName));
                    }   
                    else
                    {
                        row.Append(String.Format("\" . htmlentities($row[\"{0}\"],ENT_COMPAT,'iso-8859-1') . \"</td>", column.ColumnName));
                    }
                }
            }

            //Add the Edit and Delete links for this row
            if (database.EnablePaging && database.EnableSorting)
                row.Append(String.Format("<td class='tblCell'><a href='Edit.php?{0}=\" . $row[\"{0}\"] . \"&sort=$sortBy&pageNum=$pageNum'>Edit</a></td><td class='tblCell'><a href='index.php?action=delete&{0}=\" . $row[\"{0}\"] . \"&sort=$sortBy&pageNum=$pageNum'>Delete</a></td>", database.PKColumn));
            else if (database.EnableSorting)
                row.Append(String.Format("<td class='tblCell'><a href='Edit.php?{0}=\" . $row[\"{0}\"] . \"&sort=$sortBy'>Edit</a></td><td class='tblCell'><a href='index.php?action=delete&{0}=\" . $row[\"{0}\"] . \"&sort=$sortBy'>Delete</a></td>", database.PKColumn));
            else if (database.EnablePaging)
                row.Append(String.Format("<td class='tblCell'><a href='Edit.php?{0}=\" . $row[\"{0}\"] . \"&pageNum=$pageNum'>Edit</a></td><td class='tblCell'><a href='index.php?action=delete&{0}=\" . $row[\"{0}\"] . \"pageNum=$pageNum'>Delete</a></td>", database.PKColumn));            
            else
                row.Append(String.Format("<td class='tblCell'><a href='Edit.php?{0}=\" . $row[\"{0}\"] . \"'>Edit</a></td><td class='tblCell'><a href='index.php?action=delete&{0}=\" . $row[\"{0}\"] . \"'>Delete</a></td>", database.PKColumn));    

            return row.ToString();
        }

        /// <summary>
        /// Generates the table header markup based on fields selected in the database, creates a link for sorting if sorting enabled
        /// </summary>
        /// <param name="database">The database configuration</param>
        /// <returns>Example: echo "<tr><td><a href='?sort=FirstName&pageNum=$pageNum'>FirstName</a></td><td><a href='?sortBy=LastName&pageNum=$pageNum'>LastName</a></td><td>edit</td><td>delete</td></tr>";
        /// </returns>
        private static string BuildTableHeader(DatabaseInfo database)
        {
            //Build the row template
            StringBuilder row = new StringBuilder();

            foreach (TableColumn column in database.Columns)
            {
                //Do not display the primary key column
                if (column.ColumnName != database.PKColumn)
                {        
                    //write appropriate string based on sorting and paging
                    if(database.EnablePaging && database.EnableSorting)
                        row.Append(String.Format("<td class='tblHeaderCell'><a href='?sort={0}&pageNum=$pageNum'>{0}</a></td>", column.ColumnName));
                    else if(database.EnableSorting)
                        row.Append(String.Format("<td class='tblHeaderCell'><a href='?sort={0}'>{0}</a></td>", column.ColumnName));
                    else
                        row.Append(String.Format("<td class='tblHeaderCell'>{0}</td>", column.ColumnName));                   
                    
                }
            }

            //Add the Edit and Delete links for this row
            row.Append("<td class='tblHeaderCell'>Edit</td><td class='tblHeaderCell'>Delete</td>");

            return row.ToString();
        }

        /// <summary>
        /// Generates the select query for the edit.php page
        /// </summary>
        /// <param name="database">The database configuration</param>
        /// <returns>Example: SELECT Id, FirstName, LastName, Age FROM [PHPDB].[dbo].[Person] WHERE Id =" . $_GET["id"];</returns>
        private static string BuildEditSelectQuery(DatabaseInfo database)
        {
            //Baseline select query with where clause
            string editSelectQuery = "SELECT {0} FROM {1} WHERE {2} = ?";

            int editSelectColumnsCounter = 0;

            StringBuilder editSelectColumns = new StringBuilder();

            foreach (TableColumn column in database.Columns)
            {
                //Only append a comma after the first column has been added
                if (editSelectColumnsCounter != 0)
                {
                    editSelectColumns.Append(", ");
                }

                editSelectColumns.Append(column.ColumnName);
                editSelectColumnsCounter++;
            }

            return String.Format(editSelectQuery, editSelectColumns.ToString(), database.TableName, database.PKColumn);
        }

        /// <summary>
        /// Generates the hidden PK field for form GET
        /// </summary>
        /// <param name="database">The database configuration</param>
        /// <returns>Example: <input type='hidden' name='id' value='" . $_GET["id"] . "' />";</returns>
        private static string BuildHiddenPKField(DatabaseInfo database)
        {
            //Baseline hidden field
            string hiddenField = "<input type='hidden' name='{0}' value='\" . $_GET[\"{0}\"] . \"' />\";";

            //Generate based on primary key column
            return String.Format(hiddenField, database.PKColumn);
        }

        /// <summary>
        /// Generates the input fields for edit.php
        /// </summary>
        /// <param name="database">The database configuration</param>
        /// <returns>Example: echo "First Name: <input type='text' name='firstname' value='" . $row["FirstName"] . "'/><br>";</returns>
        private static string BuildEditInputFields(DatabaseInfo database)
        {
            int inputFieldCounter = 0;

            //Build input field columns
            StringBuilder inputFieldsBuilder = new StringBuilder();

            foreach (TableColumn column in database.Columns)
            {
                //Do not create editing fields for the primary key column
                if (column.ColumnName != database.PKColumn)
                {
                    //Add a break if this wasn't the first column added
                    if (inputFieldCounter != 0)
                    {
                        inputFieldsBuilder.Append("echo \"<br>\";");
                    }

                    // format output if this is a date field
                    if (column.ColumnType == "datetime")
                    {
                        inputFieldsBuilder.Append(String.Format("echo \"<label>{0}: </label><input type='text' name='{0}' value='\" . (isset($row[\"{0}\"]) ? $row[\"{0}\"]->format('Y-m-d H:i:s') : \"\") . \"'/>\";", column.ColumnName));
                    }
                    else
                    {
                        inputFieldsBuilder.Append(String.Format("echo \"<label>{0}: </label><input type='text' name='{0}' value='\" . $row[\"{0}\"] . \"'/>\";", column.ColumnName));
                    }
                    inputFieldCounter++;
                }
            }

            return inputFieldsBuilder.ToString();
        }

        /// <summary>
        /// Generates the input fields for create.php
        /// </summary>
        /// <param name="database">The database configuration</param>
        /// <returns>Example: echo "First Name: <input type='text' name='firstname' />";</returns>
        private static string BuildCreateInputFields(DatabaseInfo database)
        {
            int createInputFieldCounter = 0;

            //Build the list of columns to add
            StringBuilder createInputFieldsBuilder = new StringBuilder();

            foreach (TableColumn column in database.Columns)
            {
                //Do not allow the creation of a new primary key, this is done by the database
                if (column.ColumnName != database.PKColumn)
                {
                    //Add a break if this was not the first column added
                    if (createInputFieldCounter != 0)
                    {
                        createInputFieldsBuilder.Append("echo \"<br>\";");
                    }

                    createInputFieldsBuilder.Append(String.Format("echo \"<label>{0}: </label><input type='text' name='{0}' />\";", column.ColumnName));
                    createInputFieldCounter++;
                }
            }

            return createInputFieldsBuilder.ToString();
        }

        /// <summary>
        /// Generates the name value pairs of CSS for a specific CSS style
        /// </summary>
        /// <param name="database">CssCollection a set of name value pairs</param>
        /// <returns>Example: color: blue;</returns>
        private static string BuildCssNameValuePairsString(CssStyle list)
        {          
            
            StringBuilder createCssNameValuePairsBuilder = new StringBuilder();
            
            //iterate through the name value pairs
            for (int i = 0; i < list.CssProperties.Count; i++ )
            {
                if (list.CssProperties[i].CssValue != "")
                {
                    createCssNameValuePairsBuilder.Append("\t" + list.CssProperties[i].CssStyle + ": " + list.CssProperties[i].CssValue + "; \r\n");
                }
            }
            return createCssNameValuePairsBuilder.ToString();
        }

        /// <summary>
        /// Generates an IF statement to perform validation on date fields
        /// </summary>
        /// <param name="logic">A string to which PHP logic is added</param>
        /// <param name="database">The database configuration object</param>
        /// <returns>Example: if (validate_date($_GET['Date1']) { $Date1 = date('Y-m-d H:i:s', strtotime($_GET['Date1'])); $valid=true;} else { echo "errror message"; }</returns>
        public static string BuildDateValidationLogic(string logic, DatabaseInfo database)
        {
            string ifCondition = "";
            string ifLogic = "";
            int fieldsToValidate = 0;
            string errMsg = "echo \"<script language='javascript'>alert('An invalid date was specified. The database has not been modified.');</script>\";";
            string valid = "$valid=true;";

            // builds if statement parts to validate all date fields
            // output: validate_date(&_GET['Date1']) && validate_date($_GET['Date2'])
            foreach (TableColumn col in database.Columns)
            {
                if (col.ColumnType == "datetime")
                {
                    // add && operator if there is more than on DateTime field, add new line to ifLogic
                    if (fieldsToValidate > 0)
                    {
                        ifCondition = string.Concat(ifCondition, " && ");
                        ifLogic = string.Concat(ifLogic, Environment.NewLine);
                    }

                    // add to if condition, validate_date is the name of the PHP function which performs date validation
                    string field = string.Format(@"validate_date($_GET['{0}'])", col.ColumnName);
                    ifCondition = string.Concat(ifCondition, field);

                    // build logic to format date parameter for use in query
                    string formattedDate = string.Format("${0} = date('Y-m-d H:i:s', strtotime($_GET['{0}']));", col.ColumnName);
                    ifLogic = string.Concat(ifLogic, formattedDate);

                    // increment count
                    fieldsToValidate++;
                }
            }

            if (!string.IsNullOrEmpty(ifCondition) && !string.IsNullOrEmpty(ifLogic))
            {
                // create if statement from ifCondition and ifLogic
                logic = string.Format("if({0}) {{{1}{2}{1}{3}{1}}}{1}else {{{1}{4}{1}}}", ifCondition, Environment.NewLine, ifLogic, valid, errMsg);
            }

            return logic;
        }

        public static void GenerateStaticPage(DatabaseInfo database, string directory, string filename)
        {
            string fname = Path.Combine(directory, filename);
            StreamReader staticFileReader = new StreamReader(fname);
            StreamWriter staticFileWriter = new StreamWriter(Path.Combine(database.SaveFilesDir, filename));

            try
            {
                // Read the file into a string
                string file = staticFileReader.ReadToEnd();

                // Write file to disk
                staticFileWriter.Write(file);

            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                staticFileReader.Close();
                staticFileWriter.Close();
            }
        }
    }
}
